﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace NG
{
    class IndustryHandler:IIndustryhandler
    {
        private string tableName = "[dbo].[Industries]";
        public void addIndustry(Industry industry)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "INSERT INTO"+ tbn +"(industryName,industryDesc,industryExtCode) VALUES('" 
             + industry.industryName +"','"+ industry.industryDesc+"','"+industry.industryExt+ "');"  
            +"SELECT industryNo FROM" + tbn + "WHERE industryNo = @@IDENTITY;";
            SqlCommand cmd = new SqlCommand(query, con.connection);
            industry.industryNum = int.Parse(cmd.ExecuteScalar().ToString());
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't discon`nect from the Database");
            }
        }

       

        public void updateIndustry(Industry newindustry)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "UPDATE"+ tbn +" SET "+ 
                "industryName =" + "'" + newindustry.industryName + "'," + "industryDesc=" + "'" + newindustry.industryDesc+"',"+
                "industryExtCode="+"'"+newindustry.industryExt+"'"
                + "WHERE industryNo=" + newindustry.industryNum;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }





        public LinkedList<Industry> allIndustries(string sortedBy ,bool desc)
        {

            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = "{0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<Industry> industryList = new LinkedList<Industry>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT * FROM " + tbn + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    Industry industry = new Industry(int.Parse(rdr["industryNo"].ToString()),
                        rdr["industryName"].ToString(),rdr["industryDesc"].ToString(), rdr["industryExtCode"].ToString());
                   
                    industryList.AddLast(industry);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return industryList ;
        
        }


      
    }
        
}
